#include "databasemanager.h"

#include <QCoreApplication>
#include <QDebug>
#include <QFile>
#include <QHeaderView>
#include <QMessageBox>

#include <QSqlError>
#include <QSqlRecord>
#include <QSqlTableModel>
#include <QLineEdit>
#include <QInputDialog>
#include <QDate>
#include "attendancemanager.h"

const QHash<QString, QVector<std::pair<QString, QString>>> DatabaseManager::TABLE_HEADERS =
{
    {"department", {
        {"dept_id", "部门号"},
        {"dept_name", "部门名称"}
    }},
    {"employee", {
         {"emp_id", "员工号"},
         {"card_id", "卡号"},
         {"emp_name", "员工姓名"},
         {"position", "职位"},
         {"dept_id", "部门号"}
     }},
    {"attendance", {
         {"att_id", "考勤号"},
         {"emp_id", "员工号"},
         {"check_date", "日期"},
         {"clock_in", "上班打卡"},
         {"clock_out", "下班打卡"},
         {"status", "状态"}
     }},
    {"v_employee_monthly_attendance", {
         {"emp_id", "员工号"},
         {"emp_name", "员工姓名"},
         {"month", "月份"},
         {"normal_days", "正常天数"},
         {"total_days", "总天数"},
         {"attendance_rate", "出勤率"}
     }}
    // 其他表格的配置...
};

const QHash<QString, QString> DatabaseManager::TABLE_NAMES =
{
    {"部门表", "department"},  ///< 部门表索引
    {"员工表", "employee"},    ///< 员工表索引
    {"考勤表", "attendance"},  ///< 考勤表索引
    {"月出勤视图表", "v_employee_monthly_attendance"}     ///< 月出勤视图表索引
};


// QStringList DatabaseManager::getDepartments();

DatabaseManager::DatabaseManager(QTableView *tableView, QObject *parent)
    : QObject(parent)
    , m_queryModel(Q_NULLPTR)
    , m_tableModel(Q_NULLPTR)
    , m_tableView(tableView)
    , m_theSeletion(Q_NULLPTR)
    , m_pagination(Q_NULLPTR)
{
    initDatabase();// 初始化数据库连接

    // 考勤管理
    QSqlDatabase database = m_DB->database();
    m_attendance = new AttendanceManager(database, this);

    connect(m_attendance, &AttendanceManager::employeeCard, this, &DatabaseManager::employeeCard);


    // 考勤统计
    m_attendQuery = new AttendanceQuery(database, this);


}

// 连接数据库
bool DatabaseManager::connectDatabase()
{
    // 获取数据库单例实例并连接到数据库
    m_DB = SqlDatabase::instance();
    if(!m_DB->open()){
        qCritical() << "Database open failed:" << m_DB->database().lastError();
        return false;
    }
    return true;
}

// 初始化数据库
bool DatabaseManager::initDatabase()
{
    // 连接数据库
    if(!connectDatabase()) {
        return false;
    }

    // 组件初始化（整个应用生命周期只执行一次）
    static bool firstInit = true;
    if (firstInit) {
        // 模型组件（除非表结构变化否则无需重新创建）
        m_tableModel = new QSqlTableModel(this, m_DB->database());
        m_queryModel = new QSqlQueryModel(this);

        firstInit = false;
    }

    /** 初始化检查 */
    // 检查表是否存在（必须初始化）
    if (!checkTableExists(EMP_TABLE)) {
        // 检查数据是否存在数据表
        if(checkDatabaseInit())
        {
            // 执行初始化脚本
            if(!initDatabaseFile()) {
                qCritical() << "Database initialization failed";
                return false;
            }
            qInfo() << "Database initialized successfully";
        }
    } else {
        qInfo() << "Database not need initialized";
    }

    // 刷新模型（必须初始化）
    refreshTable(INI_TABLE);

    // 设置提交模式
    m_tableModel->setEditStrategy(QSqlTableModel::OnManualSubmit); // OnFieldChange(立即提交) | OnRowChange(换行提交) | OnManualSubmit(手动提交)
    m_tableView->setModel(m_tableModel);// 将模型绑定到 TableView
    m_tableModel->select();// 强制刷新，确保模型已加载数据

    //================ 动态UI配置 ================//
    // 仅在表结构有效时初始化（可重复执行）
    if (m_tableModel->columnCount() > 0) {
        initUIComponents();
    } else {
        qCritical() << "Table initialization failed after creation!";
    }

    return true;
}

// 检查表是否存在
bool DatabaseManager::checkTableExists(const QString &tableName)
{
    Q_UNUSED(tableName);

    return getTables().contains(tableName);
}

// 检查数据库是否初始化
bool DatabaseManager::checkDatabaseInit()
{
    return getTables().isEmpty();
}

// 初始化数据库
bool DatabaseManager::initDatabaseFile()
{   // 获取文件路径
    QString dbPath = QCoreApplication::applicationDirPath() + DATAbASE_INI_FILE;
    QFile sqlFile(dbPath);
    if(!sqlFile.open(QIODevice::ReadOnly)) {
        qCritical() << "Failed to open SQL file:" << sqlFile.errorString();
        return false;
    }

    QSqlDatabase::database().transaction(); // 开始事务

    try {
        QStringList sqlCommands = QString::fromUtf8(sqlFile.readAll())
        .split(';', Qt::SkipEmptyParts);

        foreach(const QString& command, sqlCommands) {
            QString trimmed = command.trimmed();
            if(trimmed.isEmpty()) continue;

            QSqlQuery query;
            if(!query.exec(trimmed)) {
                qCritical() << "SQL Error:" << query.lastError()
                << "\nFailed command:" << trimmed;
                QSqlDatabase::database().rollback();
                return false;
            }
        }
        QSqlDatabase::database().commit(); // 提交事务
        return true;
    } catch (...) {
        QSqlDatabase::database().rollback();
        return false;
    }
    return true;
}

// 获取所有数据表名
QStringList DatabaseManager::getTables() const
{
    return m_DB->database().tables();
}

QStringList DatabaseManager::getTableFields(const QString &table) const
{
    // 获取当前数据表的所有字段名
    m_tableModel->setTable(table);// 设置表格
    QSqlRecord emptyRec(m_tableModel->record());// 获取当前表格一条空记录

    // 添加到容器
    QStringList tableFields;
    for(int i = 0; i < emptyRec.count(); ++i) {
        tableFields.append(emptyRec.fieldName(i));
    }
    return tableFields;
}

// 获取表格模型
QSqlTableModel *DatabaseManager::getTableModel()
{
    return m_tableModel;
}

// 获取显示总记录数
int DatabaseManager::getTotalRecords(const QString &tableName)
{
    int RecNumber = 0;

    // 获取数据库表名
    const QString &table = DatabaseManager::TABLE_NAMES.value(tableName);

    QString queryCountSql = QString("SELECT COUNT(*) FROM %1").arg(table);

    QSqlQuery query;
    if(query.exec(queryCountSql)) {
        if(query.next()) {
            RecNumber = query.value(0).toInt();
        }
    }
    return RecNumber;
}

QStringList DatabaseManager::getDepartments()
{
    QStringList departments;
    QString queryStr = QString("SELECT dept_name AS '部门' FROM department;"); // 查询

    // 执行查询
    QSqlQuery query;
    query.exec(queryStr);
    if (query.lastError().isValid()) {
        qDebug() << "SQL Error: " << query.lastError();
        // return;
    }
    while (query.next()) {
        // 取出查询结果
        QString deptName = query.value(0).toString();
        departments << deptName;
    }
    qDebug() << "departments: " << departments;

    return departments;
}

QSqlQuery DatabaseManager::getAttendanceData(QDate queryDate, const QString &deptName)
{
    QSqlDatabase db{m_DB->database()};

    QString querySql =

    QString(R"(
        SELECT e.emp_name AS '员工姓名',
            d.dept_name AS '部门名称',
            a.check_date AS '日期',
            a.status AS '考勤状态',
            a.clock_in AS '上班打卡',
            a.clock_out AS '下班打卡'
        FROM attendance a
        JOIN employee e ON a.emp_id = e.emp_id
        JOIN department d ON e.dept_id = d.dept_id
        WHERE a.check_date = :check_date
    )");//

    // AND a.status != '正常'

    // 动态添加部门过滤条件
    if(!deptName.isEmpty()) {
        querySql += " AND d.dept_name = :dept_name";
    }


    QSqlQuery query;
    query.prepare(querySql); // 使用预处理防止SQL注入

    // 绑定参数
    query.bindValue(":check_date", queryDate.toString("yyyy-MM-dd"));
    if (!deptName.isEmpty()) {
        query.bindValue(":dept_name", deptName);
    }

    if (!query.exec()) {
        qCritical() << "Query failed:" << query.lastError().text();
    }


    // 解析结果集
#if 0
    QStringList datas;
    while (query.next()) {
        // AttendanceDetail detail;
        datas << query.value("emp_name").toString()
        << query.value("dept_name").toString()
        << query.value("check_date").toDate().toString()
        << query.value("status").toString()
        << query.value("clock_in").toTime().toString()
        << query.value("clock_out").toTime().toString();
    }
    qDebug() << datas;
#endif
    return query;
}

// 打卡
bool DatabaseManager::clockInOut(const QString &rfidCard)
{
    // 考勤打卡
    ClockResult info = m_attendance->clockInOut(rfidCard);

    qDebug() << "message:" << info.message;
    qDebug() << "clockType:" << info.clockType;
    qDebug() << "clockTime:" << info.clockTime;

    return info.success;
}

// 考勤统计数据
QSqlQuery DatabaseManager::getAttendanceStatistics(const QString &timeRange, const QString &dept, const QString &status, const QDate &date)
{
    // 考勤统计
    QSqlQuery query = m_attendQuery->setQuery(timeRange, dept, status, date);
    emit statisticsQuery(query);    // 发送考勤统计数据


    // 部门考勤率
    QSqlQuery queryDept = m_attendQuery->queryDeptAttendance(dept, timeRange, date);
    emit deptAttendance(queryDept);  // 部门考勤率


    return query;
}


// 刷新表格数据
void DatabaseManager::refreshTable(const QString& tableName, int page, int pageSize)
{
    if(!m_tableModel) return;

    if (tableName.isEmpty()) return;
        m_currentTable = tableName; // 确保表名有效

    m_currentTable = tableName;

    m_tableModel->setTable(tableName);

    // 设置表头别名
    const QVector<std::pair<QString, QString>> headers = TABLE_HEADERS.value(tableName);
    for (const auto& header : headers) {
        m_tableModel->setHeaderData(m_tableModel->fieldIndex(header.first), Qt::Horizontal, header.second);
    }

//    m_tableModel->setHeaderMap(TABLE_HEADERS.value(tableName));// 设置表头

    //
//    m_tableModel->setPagination(page, pageSize);    // 设置页数显示


    if(!m_tableModel->select()){
        emit dataOperationError(m_tableModel->lastError().text());
    }
    emit tableRefreshed();

    emit dirty(false);  // 没有脏数据了
}

void DatabaseManager::setPagination(PaginationController *pagination)
{
    m_pagination = pagination;
}

// 拆分出的UI初始化方法（可重复调用）
void DatabaseManager::initUIComponents()
{
    // 模型绑定（必须初始化但只需一次）
        if (m_tableView->model() != m_tableModel || !m_theSeletion) {
            m_tableView->setModel(m_tableModel);// 设置模型
            if(!m_theSeletion) {
                m_theSeletion = new QItemSelectionModel(m_tableModel); // 设置选择模型
                m_tableView->setSelectionModel(m_theSeletion);// 设置视图的选择模型
            }
        }

        //================ 动态UI配置 ================//
        // 列宽设置（可随窗口大小调整）
        m_tableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
        if (m_tableModel->columnCount() >= 1) {
            m_tableView->horizontalHeader()->setSectionResizeMode(0, QHeaderView::Fixed);
            m_tableView->setColumnWidth(0, 100);
        }

        // 样式配置（可动态修改）
        m_tableView->setAlternatingRowColors(true); // 交替颜色起用

        // 表头样式
        m_tableView->verticalHeader()->setDefaultSectionSize(70);
        m_tableView->horizontalHeader()->setMinimumHeight(50);
        m_tableView->horizontalHeader()->setStyleSheet("font-size:16pt;");

        // 设置选择模式和行为
        m_tableView->setSelectionMode(QAbstractItemView::ExtendedSelection);  // 多选  QAbstractItemView::ContiguousSelection
        m_tableView->setSelectionBehavior(QAbstractItemView::SelectRows);  // 选中整行
        m_tableView->setEditTriggers(QAbstractItemView::DoubleClicked);// | QAbstractItemView::SelectedClicked

        //================ 信号连接（只需一次） ================//
        // 使用lambda保证只连接一次
        static QMetaObject::Connection conn_currentChanged;
        if (!conn_currentChanged) {
            conn_currentChanged = connect(m_theSeletion, &QItemSelectionModel::currentChanged,
                          this, &DatabaseManager::do_currentChanged);
            conn_currentChanged = connect(m_theSeletion, &QItemSelectionModel::currentChanged,
                          this, &DatabaseManager::currentChanged);
        }

        static QMetaObject::Connection conn_currentRowChanged;
        if (!conn_currentRowChanged) {
            conn_currentRowChanged = connect(m_theSeletion, &QItemSelectionModel::currentRowChanged,
                          this, &DatabaseManager::do_currentRowChanged);
        }

        // ...其他信号连接同理
}



void DatabaseManager::pagingQuerySql(const QString& table, int pageNumber) {
    // 设置每页显示的记录数
    const int pageSize = m_pagination->pageSize();

    // 获取表的第一个字段名称
    QString Group;
    QStringList fieldAs;

    if(EMP_TABLE == table) {
        Group = "emp_id";
    } else if(DEPT_TABLE == table) {
        Group = "dept_id";
    } else if(ATTE_TABLE == table) {
        Group = "att_id";
    } else {
        emit dataOperationError("没有配置，这个表的数据...");

    }

    QVector<std::pair<QString, QString>> tableDat = TABLE_HEADERS.value(table);
    for(std::pair<QString, QString> &dat : tableDat)
    {
        fieldAs << QString("%1 AS '%2'").arg(dat.first).arg(dat.second);
    }

    // 分页查询：第 pageNumber 页，每页 pageSize 条
    QString queryStr = QString("SELECT %1 FROM %2 "
                               "ORDER BY %3 "
                               "LIMIT %4 "
                               "OFFSET %5;")
                       .arg(fieldAs.join(","))// 查询字段 as 别名
                       .arg(table)// 表名
                       .arg(Group)// 排序字段
                       .arg(pageSize)// 每页记录数
                       .arg((pageNumber - 1) * pageSize);// 偏移量

//    qDebug() << queryStr; // 查看SQL语句

    // 执行查询
    QSqlQuery query;
    query.exec(queryStr);
    if (query.lastError().isValid()) {
        qDebug() << "SQL Error: " << query.lastError();
        return;
    }

    // 将查询结果绑定到 QSqlQueryModel 上
    QSqlQueryModel* model = m_queryModel;
    model->setQuery(query);
    m_tableView->setModel(model);  // 刷新 TableView 显示数据
}



void DatabaseManager::onAdd()
{
    m_tableModel->insertRow(m_tableModel->rowCount(), QModelIndex());
    QModelIndex curIndex = m_tableModel->index(m_tableModel->rowCount() - 1, 1);
    m_theSeletion->clearSelection();
    m_theSeletion->setCurrentIndex(curIndex, QItemSelectionModel::Select);

    QString table = m_currentTable;

    if(EMP_TABLE == table) {
//        m_tableModel->setData(m_tableModel->index(curIndex.row(),0), 1000 + m_tableModel->rowCount());
        m_tableModel->setData(m_tableModel->index(curIndex.row(),3), "员工");
    }

    emit dirty(true);   // 有脏数据了
}

void DatabaseManager::onInsert()
{
    // 获取选中的行
    QModelIndexList selectedIndexes = m_tableView->selectionModel()->selectedRows();

    if (selectedIndexes.isEmpty()) {
        emit dataOperationError(QString("警告:" + m_tableModel->lastError().text()));
        return;
    }

    QModelIndex curIndex = m_tableView->currentIndex();
    m_tableModel->insertRow(curIndex.row(), QModelIndex());
    m_theSeletion->clearSelection();
    m_theSeletion->setCurrentIndex(curIndex, QItemSelectionModel::Select);

    emit dirty(true);   // 有脏数据了
}

void DatabaseManager::onSubmit()
{
    bool res = true;
    if(m_tableModel->isDirty()) {
        res = m_tableModel->submitAll();
    }
    if(false == res) {
        emit dataOperationError(QString("数据提交错误，错误信息:" + m_tableModel->lastError().text()));
    } else {
        emit dirty(false);  // 没有脏数据了
    }
}

void DatabaseManager::onCancel()
{
    m_tableModel->revertAll();
    emit dirty(false);  // 没有脏数据了
}

void DatabaseManager::onDelete()
{
    qDebug() << "删除选中的行";
    // 获取选中的行
    QModelIndexList selectedIndexes = m_tableView->selectionModel()->selectedRows();

    if (selectedIndexes.isEmpty()) {
        QMessageBox::warning(Q_NULLPTR, tr("警告:"), tr("请选中要更新的行"));
        return;
    }

    // 确认删除
    int reply = QMessageBox::question(Q_NULLPTR, tr("删除确认"), tr("确定要删除选中的行吗？"),
                                      QMessageBox::Yes | QMessageBox::No);
    if (reply == QMessageBox::Yes) {
        int countRow = 0;
        // 按行删除
        for (const QModelIndex &index : selectedIndexes) {
            int row = index.row();
            countRow++;
            m_tableModel->removeRow(row);
        }

        emit dataOperationError(QString("删除 %1 表 %2 条记录").arg(m_currentTable).arg(countRow));

        // 立即提交删除
//        m_tableModel->submitAll();

        // 等待手动提交
        emit dirty(true); // 有脏数据了
    }
}



// 更新选中的行
void DatabaseManager::updateSelectedRows()
{
    // 获取选中的行
    QModelIndexList selectedIndexes = m_tableView->selectionModel()->selectedRows();

    if (selectedIndexes.isEmpty()) {
        QMessageBox::warning(Q_NULLPTR, tr("警告"), tr("请选中要更新的行"));
        return;
    }

    // 假设只更新选中的第一行
    QModelIndex index = selectedIndexes.first();
    int row = index.row();

    // 获取该行数据并进行更新操作（示例：仅更新 Username 字段）
    QString username = m_tableModel->data(m_tableModel->index(row, m_tableModel->fieldIndex("Username"))).toString();

    bool ok;
    QString newUsername = QInputDialog::getText(Q_NULLPTR, tr("更新用户名"), tr("请输入新的用户名："), QLineEdit::Normal, username, &ok);

    if (ok && !newUsername.isEmpty()) {
        m_tableModel->setData(m_tableModel->index(row, m_tableModel->fieldIndex("Username")), newUsername);
        m_tableModel->submitAll();  // 提交更改
    }
}



void DatabaseManager::do_currentChanged(QModelIndex current, QModelIndex perivous)
{
    Q_UNUSED(current);
    Q_UNUSED(perivous);

    qDebug() << "do_currentChanged 进来了...";

    emit dirty(m_tableModel->isDirty());
}

void DatabaseManager::do_currentRowChanged(QModelIndex current, QModelIndex perivous)
{
    Q_UNUSED(perivous);

    emit isSeletRow(current.isValid());
}


// 检查部门是否存在
bool DatabaseManager::departmentExists(const QString &deptName) {
    QSqlQuery query;
    query.prepare("SELECT dept_id FROM department WHERE dept_name = ?");
    query.addBindValue(deptName);

    return query.exec() && query.next();
}

// 获取部门ID（不存在时返回-1）
int DatabaseManager::getDepartmentId(const QString &deptName) {
    QSqlQuery query;
    query.prepare("SELECT dept_id FROM department WHERE dept_name = ?");
    query.addBindValue(deptName);
    return query.exec() && query.next() ? query.value(0).toInt() : -1;
}

// 注册新员工（返回生成的员工ID，失败返回-1）
int DatabaseManager::registerEmployee(const QString &cardId,
                     const QString &name,
                     const QString &deptName,
                     const QString &position)
{
    m_DB->database().transaction();

    qDebug() << "registerEmployee 进来了";
    try {
        // 验证部门存在性
        int deptId = getDepartmentId(deptName);
        if(deptId == -1) {
            qWarning() << "Department not exists:" << deptName;
            m_DB->database().rollback();
            return -1;
        }

        // 生成员工ID
        QSqlQuery maxQuery("SELECT MAX(emp_id) FROM employee");
        int newEmpId = maxQuery.next() ? maxQuery.value(0).toInt() + 1 : 10001;

        qDebug() << "生成员工ID:" << newEmpId;
        // 插入新员工
        QSqlQuery insertQuery;
        insertQuery.prepare(
            "INSERT INTO employee "
            "(emp_id, card_id, emp_name, position, dept_id) "
            "VALUES (?, ?, ?, ?, ?)");

        insertQuery.addBindValue(newEmpId);
        insertQuery.addBindValue(cardId);
        insertQuery.addBindValue(name);
        insertQuery.addBindValue(position.isEmpty() ? "员工" : position);
        insertQuery.addBindValue(deptId);

        if(!insertQuery.exec()) {
            qWarning() << "Insert failed:" << insertQuery.lastError();
            m_DB->database().rollback();
            qDebug() << "插入失败";
            return -1;
        }

        m_DB->database().commit();
        return newEmpId;
    }
    catch (...) {
        m_DB->database().rollback();
        qDebug() << "操作失败:抛出异常";
        return -1;
    }
}
